#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
import json
import numpy as np
from collections import defaultdict
import dianputools
from product_data import get_product_exchange
from sqlquery import *


def get_product_city(p):
    dbh = dianputools.connect_to_shangpin()
    query = 'select local from t_product where ProductID = "%s";' % (p)
    city = dianputools.get_a_string(dbh, query)
    if city == None:
        return 0
    if city == "":
        return 0
    
    return  json.loads(city)[0]['L']
    
def get_city_fromJson(local):
    
    if local == None:
        return 0
    if local == "":
        return 0
    
    return  json.loads(local)[0]['L']

def get_orderid_city(o):
    dbh = dianputools.connect_to_dingdan()
    query = 'select cityid from t_order where orderid = "%s";' % (o)
    city = dianputools.get_a_number(dbh, query)
    return city


def get_product_info(dtstart, dtend, p):
    desc = []
    querys = []

    desc.append('bookingproduct')
    q = query_num_of_boking_product(dtstart, dtend, p)
    querys.append(q)

    # one order may have many products
    desc.append('bookingorder')
    q = query_num_of_boking_order(dtstart, dtend, p)
    querys.append(q)

    desc.append('bookingproductfee')
    q = query_num_of_boking_order_fee(dtstart, dtend, p)
    querys.append(q)

    desc.append('paidorder')
    q = query_num_of_paid_order(dtstart, dtend, p)
    querys.append(q)

    desc.append('productselled')
    q = query_num_of_paid_product(dtstart, dtend, p)
    querys.append(q)

    desc.append('paidfee')
    q = query_num_of_order_total_fee(dtstart, dtend, p)
    querys.append(q)

    desc.append('paiduser')
    q = query_num_of_user_paid(dtstart, dtend, p)
    querys.append(q)

    desc.append('newpaiduser')
    q = query_num_of_new_user_paid(dtstart, dtend, p)
    querys.append(q)

    dbh = dianputools.connect_to_dingdan()
    nums = [dianputools.get_a_number(dbh, q) for q in querys]

    desc.append('bookingorderfeeperorder')
    nums.append(1.0 * nums[2] / nums[6])

    return desc, nums


def get_orderproduct_stats(dtstart, dtend, map_product_to_key=get_product_city):
    city2product = defaultdict(list)
    products =  get_product_exchange(dtstart, dtend)
    desc = {}
    for p,t in products:
        city = map_product_to_key(p)
        desc, pstats = get_product_info(dtstart, dtend, p)

        city2product[city].append(np.array(pstats))

    oo = {}
    for k, v in city2product.items():
        oo[k] = list(sum(v))

    return desc, oo


def get_product_flux(day, pid):
    dbh = dianputools.connect_to_50()
    flux_table = 'product_flux_day'
    q = 'select pv,visits,uv,new_uv from %s where stat_date="%s" and product_id="%s"' % (flux_table, day, pid)
    # print q
    field = ['pv', 'visits', 'uv', 'newvisitor']
    try:
        sumflux = dianputools.query(dbh, q)[0]
    except:
        sumflux = [0] * len(field)

    return field, sumflux


def get_city_flux(dtstart, dtend, map_product_to_key=get_product_city):
    city2product = defaultdict(list)
    products =  get_product_exchange(dtstart, dtend)
    # print products
    desc = {}
    for p,t in products:
        city = map_product_to_key(p)
        desc, pstats = get_product_flux(dtstart, p)

        city2product[city].append(np.array(pstats, np.int))

    oo = {}
    for k, v in city2product.items():
        oo[k] = list(sum(v))

    return desc, oo



def get_refund_stats(s, e):
    dbh = dianputools.connect_to_dingdan()

    desc = ['refundorder', 'refundproduct', 'refunduser', 'refundfee']
    query = 'select o.cityid, count(distinct rr.orderid), count(distinct rr.childorderid), count(distinct rr.buyerid), sum(rr.refundamount) from refundrequest rr left outer join t_order o on rr.orderid = o.orderid where rr.refundstate=3 and rr.refunddate > "%s" and rr.refunddate < "%s" group by o.cityid' % (s, e)
    oo = dianputools.query(dbh, query)
    city2refund = {}
    for o in oo:
        city2refund[int(o[0])] = o[1:]

    return desc, city2refund


def get_orderticket_stats(s, e):
    dbh = dianputools.connect_to_dingdan()

    desc = ['consumedticket', 'consumedorder', 'consumedfee']

    # num of ticket consumed
    query = 'select cityid, count(1) from t_orderticket where tickettype=0 and lifestate=0 and state=0 and consumedate > "%s" and consumedate < "%s" group by cityid' % (s, e)
    oo = dianputools.query(dbh, query)
    ticketcity = {}
    for k, v in oo:
        ticketcity[int(k)] = int(v)

    # num of order consumed
    query = 'select cityid, count(1) from t_orderticket ot left outer join t_ticketlog tl on tl.ticketid = ot.id where tl.cashdate > "%s" and tl.cashdate < "%s" group by cityid;' % (s, e)
    oo = dianputools.query(dbh, query)
    ordercity = {}
    for k, v in oo:
        ordercity[int(k)] = int(v)

    # fee consumed
    query = 'select ot.cityid, sum(op.price) from t_orderproduct op left outer join t_orderticket ot on op.id=ot.orderproductid left outer join t_ticketlog otl on ot.id=otl.ticketid where otl.cashdate > "%s" and otl.cashdate < "%s" group by ot.cityid' % (s, e)
    oo = dianputools.query(dbh, query)
    feecity = {}
    for k, v in oo:
        feecity[int(k)] = float(v)

    return desc, ticketcity, ordercity, feecity


def get_comment_stats(s, e):
    dbh = dianputools.connect_to_dianpu()
    q = 'select orderid, type from comment where date >= "%s" and date < "%s"' % (s, e)
    order_and_type = dianputools.query(dbh, q)

    commentcity = defaultdict(list)
    for a, b in order_and_type:
        city = get_orderid_city(a)
        commentcity[city].append(b)

    desc = ['comment', 'goodcomment', 'badcomment']

    scomment = {}
    for k, v in commentcity.items():
        n, g, b = dianputools.filter_total_good_bad_comment(v)
        scomment[k] = [n, g, b]

    return desc, scomment


def hoho(dbh, q):
    local_id = dianputools.query(dbh, q)
    onlineshop = defaultdict(set)
    for local, id in local_id:
        city = 0 if local is None or local == '' else json.loads(local)[0]['D']
        onlineshop[city].add(id)
    for k in onlineshop.keys():
        onlineshop[k] = len(onlineshop[k])
    return onlineshop


def get_online(s, e):
    dbh = dianputools.connect_to_dianpu()
    q = 'select local, id from shop where state="1"'
    onlineshop = hoho(dbh, q)

    dbh = dianputools.connect_to_shangpin()
    q = 'select local, ProductID from t_product where state="1" and salestate="1"'
    onlineproduct = hoho(dbh, q)

    q = 'select local, ProductID from t_product where state="1" and salestate="1" and date(AddDate)="%s"' % (s)
    shangxianproduct= hoho(dbh, q)

    return ['onlinedianpu', 'onlineproduct', 'shangxianproduct'], onlineshop, onlineproduct, shangxianproduct


def updaterow(dbh, day, cityids, desc, cdict):
    tablename = 'city_stats'
    for cid in cdict:
        if cid not in cityids:
            cityids.append(cid)
            dianputools.insertinto(dbh, tablename, ['day', 'city'], [day, cid])

    for c, v in cdict.items():
        restriction = 'day="%s" and city="%d"' % (day, c)
        dianputools.update(dbh, tablename, desc, v, restriction)


if __name__ == '__main__':
    DEBUG = False

    if len(sys.argv) != 4:
        # print 'usage: %s year month day' % (sys.argv[0])
        # sys.exit(1)
        year, month, day = 2012, 4, 16
    else:
        year, month, day = map(int, sys.argv[1:])
    print year, month, day

    dtstart, dtend = dianputools.str_day_start_and_end(year, month, day)

    cityids = []
    desc = []

    dbh = dianputools.connect_to_50()
    tablename = 'city_stats'

    d, orderproduct = get_orderproduct_stats(dtstart, dtend)
    updaterow(dbh, dtstart, cityids, d, orderproduct)
    desc.extend(d)
    if DEBUG:
        print '=' * 60
        print orderproduct

    d, xxflux = get_city_flux(dtstart, dtend)
    updaterow(dbh, dtstart, cityids, d, xxflux)
    desc.extend(d)

    d, refund =  get_refund_stats(dtstart, dtend)
    updaterow(dbh, dtstart, cityids, d, refund)
    desc.extend(d)
    if DEBUG:
        print '=' * 60
        print refund

    d, ticketcity, ordercity, feecity = get_orderticket_stats(dtstart, dtend)
    updaterow(dbh, dtstart, cityids, d[0], ticketcity)
    updaterow(dbh, dtstart, cityids, d[1], ordercity)
    updaterow(dbh, dtstart, cityids, d[2], feecity)
    desc.extend(d)
    if DEBUG:
        print '=' * 60
        print ticketcity, ordercity, feecity



    # d, ticketcity, ordercity, feecity = get_online(dtstart, dtend)
    # updaterow(dbh, dtstart, cityids, d[0], ticketcity)
    # updaterow(dbh, dtstart, cityids, d[1], ordercity)
    # updaterow(dbh, dtstart, cityids, d[2], feecity)
    # desc.extend(d)
    # if DEBUG:
    #     print '=' * 60
    #     print ticketcity, ordercity, feecity



    d, commentcity = get_comment_stats(dtstart, dtend)
    updaterow(dbh, dtstart, cityids, d, commentcity)
    desc.extend(d)
    if DEBUG:
        print '=' * 60
        print commentcity

    if DEBUG:
        print '=' * 60
        print desc

